package com.aote.rs;

import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.Blob;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import javax.transaction.Transactional;
import javax.ws.rs.Consumes;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.WebApplicationException;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import org.glassfish.jersey.media.multipart.FormDataContentDisposition;
import org.glassfish.jersey.media.multipart.FormDataParam;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.collection.internal.PersistentSet;
import org.hibernate.engine.spi.CascadeStyle;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.persister.entity.AbstractEntityPersister;
import org.hibernate.persister.entity.EntityPersister;
import org.hibernate.type.CollectionType;
import org.hibernate.type.ManyToOneType;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.aote.util.ExcelUtil;


/**
 * 元数据服务
 * 
 *
 */
@Path("db")
@Scope("prototype")
@Component
@Transactional
public class ChengranService {
	static Logger log = Logger.getLogger(ChengranService.class);
	
	static Map<String, String> f_state_map = new HashMap<String, String>();
	static Map<String, String> f_last_check_state_map = new HashMap<String, String>();
	static Map<String, String> f_user_type_map = new HashMap<String, String>();
	static Map<String, String> f_meter_diameter_map = new HashMap<String, String>();
	static Map<String, String> f_meter_measurement_map = new HashMap<String, String>();
	
	static {
		f_last_check_state_map.put("入户", "入户");
		f_last_check_state_map.put("到访不遇", "到访不遇");
		f_last_check_state_map.put("拒检", "拒检");
		
		f_state_map.put("正常", "正常");
		f_state_map.put("注销", "注销");
		f_state_map.put("点火", "点火");
		
		f_user_type_map.put("居民", "居民");
		f_user_type_map.put("商业", "商业");
		
		f_meter_diameter_map.put("G1.6", "G1.6");
		f_meter_diameter_map.put("G2.5", "G2.5");
		f_meter_diameter_map.put("G4", "G4");
		f_meter_diameter_map.put("G6", "G6");
		f_meter_diameter_map.put("G16", "G16");
		f_meter_diameter_map.put("G25", "G25");
		f_meter_diameter_map.put("G10", "G10");

		f_meter_measurement_map.put("流量计", "流量计");
		f_meter_measurement_map.put("膜式表", "膜式表");
		
	}
	
	@Autowired
	private SessionFactory sessionFactory;

	// 保存文件
	@SuppressWarnings("finally")
	@Path("savefile")
	@POST
	public String savefile(byte[] file,
			@QueryParam("FileName") String filename,
			@QueryParam("BlobId") String blob_id,
			@QueryParam("EntityName") String EntityName) {
		String result = null;
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			map.put("filename", filename);
			map.put("id", blob_id);
			Session session = sessionFactory.getCurrentSession();
			map.put("blob", Hibernate.getLobCreator(session).createBlob(file));
			session.saveOrUpdate(EntityName, map);
			session.flush();
			result = "";
		} catch (Exception e) {
			throw new WebApplicationException(500);
		} finally {
			return result;
		}
	}

	
	@POST
	@Path("importExcel")
	@Consumes(MediaType.MULTIPART_FORM_DATA)
	@Transactional(value = Transactional.TxType.NEVER)
	public String importExcel(@FormDataParam("file") InputStream is, @FormDataParam("file") FormDataContentDisposition fileDisposition) {
		try {
			JSONObject jo = new JSONObject();
			jo.put("code", 500);
			
			Workbook wb = getWorkBook(is);
			if(wb == null) {
				jo.put("result", "打开导入文件错误，请检查文件格式是否正确");
				return jo.toString();
			}
			Session session = this.sessionFactory.getCurrentSession();
			session.beginTransaction();
			String result =  readSheet(wb, 0);
			if(result == null) {
				jo.put("result", "请检查导入文件格式是否正确");
				session.getTransaction().rollback();
				return jo.toString();
			}
			else if(result.length() > 0) {
				jo.put("result", result);
				session.getTransaction().rollback();
				return jo.toString();
			}
			else {
				session.getTransaction().commit();
				return "{\"code\":200}";
			}
		}
		catch(JSONException e) {
			return "{\"code\":500, \"result\":\"未知错误，请联系管理员。\"}";
		}
	}
	

	private String readSheet(Workbook wb, int sheetNo) {
		Sheet sheet = wb.getSheetAt(sheetNo);
		// 获得当前sheet的开始行
		int firstRowNum = sheet.getFirstRowNum();
		// 获得当前sheet的结束行
		int lastRowNum = sheet.getLastRowNum();
		Row row = sheet.getRow(firstRowNum);
		int firstCell = row.getFirstCellNum();
		int lastCell = firstCell + 25;
		try {
		if(!(row.getCell(firstCell).getStringCellValue().equals("所属分公司") && 
			row.getCell(lastCell).getStringCellValue().equals("备注")))
			return null;
		}
		catch(Exception e) {
			return null;
		}
		HashMap<String, String> hm = new HashMap<String, String>();
		// 循环除了第一行的所有行
		for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
			// 获得当前行
			row = sheet.getRow(rowNum);
			if (row == null) {
				continue;
			}
			String[] line = new String[26];
			DataFormatter formatter = new DataFormatter();
			// 循环当前行
			for (int cellNum = firstCell; cellNum <= lastCell; cellNum++) {
				Cell cell = row.getCell(cellNum);
				line[cellNum-firstCell] = formatter.formatCellValue(cell);
			}
			
			String lineError = importARow(rowNum, line, hm);
			if(lineError != null)
				return lineError;
		}
		return "";
	}

	
	private String importARow(int rowNum, String[] line, HashMap<String, String> checkerMap) {
		try {
			trimAllCols(line);
			String f_checker = line[13];
			String f_subcompany = line[0];
			if(isNullOrEmpty(f_subcompany)) 
				return "第" + rowNum + "行没有所属分公司";
			if(isNullOrEmpty(f_checker)) 
				return "第" + rowNum + "行没有安检负责人";
			f_subcompany = f_subcompany.trim();
			f_checker = f_checker.trim();
			String f_checker_id;
			Session session = sessionFactory.getCurrentSession();
			if(checkerMap.containsKey(f_checker))
				f_checker_id = checkerMap.get(f_checker);
			else {
				List list = session.createQuery("from t_user where name='"
						+ f_checker + "'").list();
				if (list.size() != 1)
					return "第" + rowNum + "行找不到安检员" + f_checker;
				else {
					f_checker_id = ((Map)list.get(0)).get("id") + "";
					checkerMap.put(f_checker, f_checker_id);
				}
			}
			String f_user_id = line[1];
			if(isNullOrEmpty(f_user_id)) 
				return "第" + rowNum + "行没有用户编号";
			f_user_id = f_user_id.trim();
			List list = session.createQuery("from t_userfiles where f_user_id = '" + f_user_id + "'").list();
			if(list.size() != 0)
				return "第" + rowNum + "行用户编号重复";
			String f_user_name = line[2];
			if(isNullOrEmpty(f_user_name)) 
				return "第" + rowNum + "行没有用户名";
			f_user_name = f_user_name.trim();
			String f_user_phone = line[3];
			if(isNullOrEmpty(f_user_phone)) 
				return "第" + rowNum + "行没有电话";
			f_user_phone = f_user_phone.trim();
			String f_residential_area = line[4];
			if(isNullOrEmpty(f_residential_area)) 
				return "第" + rowNum + "行没有小区";
			String f_unit = line[6];
			if(isNullOrEmpty(f_unit)) 
				return "第" + rowNum + "行没有单元";
			String f_building = line[5];
			if(isNullOrEmpty(f_building)) 
				return "第" + rowNum + "行没有楼号";
			String f_floor = line[7];
			if(isNullOrEmpty(f_floor)) 
				return "第" + rowNum + "行没有楼层";
			String f_room = line[8];
			if(isNullOrEmpty(f_room)) 
				return "第" + rowNum + "行没有房号";
			String f_address = line[9];
			if(isNullOrEmpty(f_address)) 
				return "第" + rowNum + "行没有地址";
			String f_last_check_state = line[10];
			if(isNullOrEmpty(f_last_check_state)) 
				return "第" + rowNum + "行没有安检状态";
			if(!f_last_check_state_map.containsKey(f_last_check_state)) {
				return "第" + rowNum + "行安检状态必须为入户、到访不遇、拒检";
			}
			String f_last_check_date = line[12];
			if(isNullOrEmpty(f_last_check_date)) 
				return "第" + rowNum + "行没有安检时间";
			String f_archive_date = line[11];
			if(isNullOrEmpty(f_archive_date)) 
				return "第" + rowNum + "行没有建档时间";
			String f_user_type = line[14];
			if(isNullOrEmpty(f_user_type)) 
				return "第" + rowNum + "行没有用户类型";
			if(!f_user_type_map.containsKey(f_user_type)) {
				return "第" + rowNum + "行用户类型必须为居民或商业";
			}
			String f_state = line[15];
			if(isNullOrEmpty(f_state)) 
				return "第" + rowNum + "行没有档案状态";
			if(!f_state_map.containsKey(f_state)) {
				return "第" + rowNum + "行档案状态必须为正常、注销、点火";
			}
			String f_remark = line[25];
			
			Map<String, Object> map = new HashMap<String, Object>();
			f_last_check_date = formatDate(f_last_check_date);
			if(f_last_check_date == null) 
				return "第" + rowNum + "行安检日期格式出错，必须类似2015-5-30或5/30/2015";
			map.put("f_last_check_date", f_last_check_date);
			map.put("f_last_check_state", f_last_check_state);
			map.put("f_checker", f_checker);
			map.put("f_checker_id", f_checker_id);
			map.put("f_user_name", f_user_name);
			map.put("f_user_phone", f_user_phone);
			map.put("f_user_id", f_user_id);
			map.put("f_subcompany", f_subcompany);
			f_archive_date = formatDate(f_archive_date);
			if(f_archive_date == null) 
				return "第" + rowNum + "行建档日期格式出错，必须类似2015-5-30或5/30/2015";
			map.put("f_archive_date", f_archive_date);
			map.put("f_user_type", f_user_type);
			map.put("f_residential_area", f_residential_area);
			map.put("f_building", f_building);
			map.put("f_unit", f_unit);
			map.put("f_floor", f_floor);
			map.put("f_room", f_room);
			map.put("f_address", f_address);
			map.put("f_sign", "0");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			map.put("f_inputdate", sdf.format(new Date()));
			map.put("f_state", f_state);
			if(f_remark != null)
				map.put("f_remark", f_remark);
			
			String f_card_id = line[16];
			if(isNullOrEmpty(f_card_id)) 
				return "第" + rowNum + "行没有卡号";
			String f_meter_no = line[17];
			if(isNullOrEmpty(f_meter_no)) 
				return "第" + rowNum + "行没有表号";
			String f_meter_brand = line[18];
			if(isNullOrEmpty(f_meter_brand)) 
				return "第" + rowNum + "行没有表品牌";
			String f_meter_measurement = line[19];
			if(isNullOrEmpty(f_meter_measurement)) 
				return "第" + rowNum + "行没有表计量类型";
			if(!f_meter_measurement_map.containsKey(f_meter_measurement)) {
				return "第" + rowNum + "行表计量类型必须为膜式表、流量计";
			}
			String f_meter_diameter = line[20];
			if(isNullOrEmpty(f_meter_diameter)) 
				return "第" + rowNum + "行没有表径";
			if(!f_meter_diameter_map.containsKey(f_meter_diameter)) {
				return "第" + rowNum + "行表径必须为G1.6、G2.5 G4 G6  G10  G16  G25 ";
			}
			String f_meter_reading = line[21];
			if(isNullOrEmpty(f_meter_reading)) 
				return "第" + rowNum + "行没有基表读数";
			String f_meter_remain = line[22];
			if(isNullOrEmpty(f_meter_remain)) 
				return "第" + rowNum + "行没有剩余气量";
			String f_meter_total = line[23];
			if(isNullOrEmpty(f_meter_total)) 
				return "第" + rowNum + "行没有总用气量";
			String f_meter_accumulation = line[24];
			if(isNullOrEmpty(f_meter_accumulation)) 
				return "第" + rowNum + "行没有总购气量";
			

			HashMap map2 = new HashMap<String, Object>();
			map2.put("f_user_id", f_user_id);
			map2.put("f_card_id", f_card_id);
			map2.put("f_meter_no", f_meter_no);
			map2.put("f_meter_brand", f_meter_brand);
			map2.put("f_meter_measurement", f_meter_measurement);
			map2.put("f_meter_diameter", f_meter_diameter);
			try {
				map2.put("f_meter_reading", Integer.parseInt(f_meter_reading));
			}
			catch(Exception e) {
				return "第" + rowNum + "行基表读数不是数字";
			}
			try {
				map2.put("f_meter_remain", Integer.parseInt(f_meter_remain));
			}
			catch(Exception e) {
				return "第" + rowNum + "行剩余气量不是数字";
			}
			try {
				Integer.parseInt(f_meter_total);
				map2.put("f_meter_total", f_meter_total);
			}
			catch(Exception e) {
				return "第" + rowNum + "行总用气量不是数字";
			}
			try {
				Integer.parseInt(f_meter_accumulation);
				map2.put("f_meter_accumulation", f_meter_accumulation);
			}
			catch(Exception e) {
				return "第" + rowNum + "行累积购气量不是数字";
			}
			
			map.put("f_card_id", f_card_id);
			session.saveOrUpdate("t_userfiles", map);
			
			
			String f_userfile_id = (String) map.get("id");
			map2.put("f_userfile_id", f_userfile_id);
			session.save("t_meterfiles", map2);
			
			return null;
		}
		catch(Exception e) {
			return "未知错误";
		}
	}


	private void trimAllCols(String[] line) {
		for(int i=0; i<line.length; i++) {
			if(!isNullOrEmpty(line[i]))
				line[i] = line[i].trim();
		}
	}


	private boolean isNullOrEmpty(String str) {
		return str == null || str.trim().length() == 0;
	}


	private String formatDate(String adate) {
		if(adate.indexOf('/') != -1)
			return formatDate(adate, '/');
		if(adate.indexOf('-') != -1)
			return formatDate(adate, '-');
		return null;
	}


	private String formatDate(String strDate, char delimiter) {
		try {
			//parse year, day, month and make it a real date
			Date aDate = null;
			if(delimiter == '/') {
				int pos = strDate.lastIndexOf('/');
				int y = Integer.parseInt(strDate.substring(pos+1));
				int pos2 = strDate.indexOf('/');
				int m = Integer.parseInt(strDate.substring(0, pos2));
				int d = Integer.parseInt(strDate.substring(pos2+1, pos));
				if(y <1900 || y >2050 )
					return null;
				aDate = new Date(y-1900, m-1, d);
			} else {
				int pos = strDate.lastIndexOf('-');
				int d = Integer.parseInt(strDate.substring(pos+1));
				int pos2 = strDate.indexOf('-');
				int y = Integer.parseInt(strDate.substring(0, pos2));
				int m = Integer.parseInt(strDate.substring(pos2+1, pos));
				if(y <1900 || y >2050 )
					return null;
				aDate = new Date(y-1900, m-1, d);
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			return sdf.format(aDate);
		}
		catch(Exception e) {
			return null;
		}
	}


	private static Workbook getWorkBook(InputStream is) {
		try {
			//创建Workbook工作薄对象，表示整个excel  
	        Workbook workbook = null;  
	        workbook = WorkbookFactory.create(is);			// 获取excel文件的io流
			return workbook;
		}
		catch(Exception e) {
			return null;
		}
	}


	// 获得图片
	@Path("file/{blobid}")
	public String getimage(@Context HttpServletResponse response,
			@PathParam("blobid") String blobid) {
		try {
			Session session = sessionFactory.getCurrentSession();
			List list = session.createQuery("from t_blob where id='"
					+ blobid + "'").list();
			if (list.size() == 0)
				return "";
			Map map = (Map) list.get(0);
			// 获得文件名
			String filename = (String) map.get("filename");
			filename = URLEncoder.encode(filename, "UTF-8");
			// 获得文件
			Blob file = (Blob) map.get("blob");
			response.setStatus(HttpServletResponse.SC_OK);
			response.setContentType("application/octet-stream");
			response.setHeader("Content-Disposition", "attachment;filename=\""
					+ filename + "\"");
			// 把文件的内容送入响应流中
			InputStream is = file.getBinaryStream();
			OutputStream os = new BufferedOutputStream(response
					.getOutputStream());
			transformStream(is, os);
			is.close();
			os.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	public void transformStream(InputStream is, OutputStream os) {
		try {
			byte[] buffer = new byte[1024];
			// 读取的实际长度
			int length = is.read(buffer);
			while (length != -1) {
				os.write(buffer, 0, length);
				length = is.read(buffer);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
}
